using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

using System.Data.Common;

using TestMethod.Test;
namespace TestMethod.Test
{
   
public class TESTDao

{
				private const string colID1 ="ID1";
				private const string colNAME1 ="NAME1";
				private const string colAGE1 ="AGE1";
				private const string colBRITHDAY ="BRITHDAY";
				private const string colREMARK ="REMARK";
				private const string colADDRESS ="ADDRESS";
				private const string colADDRESS2 ="ADDRESS2";
				private const string colADDRESS3 ="ADDRESS3";
				private const string colADDRESS4 ="ADDRESS4";
				private static readonly string[] ColumnCollections = new[] { colID1,colNAME1,colAGE1,colBRITHDAY,colREMARK,colADDRESS,colADDRESS2,colADDRESS3,colADDRESS4};
        private readonly string _columnCollectionSelectString = string.Join(",", ColumnCollections);
        private static readonly string[] KeyColumns = new string[] { "ID1" };
        private   readonly string WhereClause = KeyColumns.JoinFormat(" AND ", "{0} =:{0}");
		
		DbHelper dbDal=null;
      
        public TESTDao()
        {
            dbDal=new DbHelper();
        }

        public TESTDao(DbHelper dbDal)
        {
            this.dbDal = dbDal;
        }
		
		private string GetSortText(SortDefinitions sortDefinitions)
		{
			string sortDefinition;
            if(sortDefinitions!=null && sortDefinitions.SoftItems.Count>0)
            {
                sortDefinition = string.Join(" , ", sortDefinitions.SoftItems.Select(p=> string.Format("{0} {1}", p.ColumnName , (p.SortOrder==SortOrder.None)?"":p.SortOrder.ToString())));
				
				sortDefinition = string.Format(" ORDER BY {0}", sortDefinition);				
            }
			else
			{
				 sortDefinition = "ORDER BY " + string.Join(",", KeyColumns);
			}		
			return sortDefinition;
		}
		
		public List<TEST> GetPage( SortDefinitions sortDefinitions , int startIndex, int pageSize)
        {
		    string sqlText;
			var sortText = GetSortText(sortDefinitions);
			if(startIndex>-1 && pageSize>0)
			{
			  sqlText = GetPagedQuery(sortText, startIndex, pageSize);
			}
			else
			 sqlText = GettSelectSQL();

			  var command = dbDal.GetSqlStringCommond(sqlText);
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);            
        }
		
		private TEST From(DataRow dr)
        {
            TEST tmp = new TEST();
					    if(dr[colID1]!=null)
			{
		    tmp.ID1=(string)dr[colID1];
			}
				    if(dr[colNAME1]!=null)
			{
		    tmp.NAME1=(string)dr[colNAME1];
			}
				    if(dr[colAGE1]!=null)
			{
		    tmp.AGE1=(string)dr[colAGE1];
			}
				    if(dr[colBRITHDAY]!=null)
			{
		    tmp.BRITHDAY=(string)dr[colBRITHDAY];
			}
				    if(dr[colREMARK]!=null)
			{
		    tmp.REMARK=(string)dr[colREMARK];
			}
				    if(dr[colADDRESS]!=null)
			{
		    tmp.ADDRESS=(Decimal)dr[colADDRESS];
			}
				    if(dr[colADDRESS2]!=null)
			{
		    tmp.ADDRESS2=(string)dr[colADDRESS2];
			}
				    if(dr[colADDRESS3]!=null)
			{
		    tmp.ADDRESS3=(string)dr[colADDRESS3];
			}
				    if(dr[colADDRESS4]!=null)
			{
		    tmp.ADDRESS4=(string)dr[colADDRESS4];
			}
				
            return tmp;
        }

        private List<TEST> FromDt(DataTable dt)
        {
            List<TEST> list = new List<TEST>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(From(dr));
            }
            return list;
        }
		
		

        public List<TEST> GetAll()
        {

             var command = dbDal.GetSqlStringCommond(GettSelectSQL());
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);     
        }

        		public TEST GetByKey(TEST test)
        {
           TEST tml = new TEST();

            var command = dbDal.GetSqlStringCommond(GetSelectSQLWithKey());
           	AddKeyColumns(command, test.ID1);
            DataTable dt = dbDal.ExecuteDataTable(command);
             List<TEST> list=FromDt(dt);
             if (list.Count > 0)
             {
                 tml = list[0];
             }
             return tml;  
		}
		
		private string GetSelectSQLWithKey()
        {
            var sql = string.Format(@"SELECT  {0}
                            FROM 
                                TESTS
                            WHERE
                                {1}
                                       ", _columnCollectionSelectString, WhereClause);
            return sql;
        }		

		        public long Insert(TEST test)
        { 			
			var command = dbDal.GetSqlStringCommond(InsertSQL());
		               AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command);
						return 1;
			        }

		public long Insert(TEST test, Trans t)
        { 			
			var command = dbDal.GetSqlStringCommond(InsertSQL());
		               AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command,t);
						return 1;
			        }

        public void Update(TEST test)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command);
        }

		 public void Update(TEST test,Trans t)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command,t);
        }

        public void Delete(TEST test)
        {
            var command =  dbDal.GetSqlStringCommond(DeletetSQL());
						AddKeyColumns(command, test.ID1);
			            dbDal.ExecuteNonQuery(command);
        }

		public void Delete(TEST test,Trans t)
        {
            var command =  dbDal.GetSqlStringCommond(DeletetSQL());
						AddKeyColumns(command, test.ID1);
			            dbDal.ExecuteNonQuery(command,t);
        }
				private void AddKeyColumns(DbCommand command, params  object[] paramValues)
        {
            for (int i = 0; i < KeyColumns.Count(); i++)
            {
                DbParameter parameter = command.CreateParameter();
                parameter.ParameterName = string.Format(":{0}", KeyColumns[i]);
                parameter.Value = paramValues[i];
                command.Parameters.Add(parameter);
            }
           
        }
				
		private void AddInsertParameters(DbCommand command, TEST test)
        {
            DbParameter parameter = null;
        			    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colID1);
			            parameter.Value = test.ID1;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colNAME1);
			            parameter.Value = test.NAME1;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colAGE1);
			            parameter.Value = test.AGE1;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colBRITHDAY);
			            parameter.Value = test.BRITHDAY;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colREMARK);
			            parameter.Value = test.REMARK;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colADDRESS);
			            parameter.Value = test.ADDRESS;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colADDRESS2);
			            parameter.Value = test.ADDRESS2;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colADDRESS3);
			            parameter.Value = test.ADDRESS3;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(":{0}",colADDRESS4);
			            parameter.Value = test.ADDRESS4;
            			command.Parameters.Add(parameter);
		
			           
        }		
		
		private void AddUpdateParameters(DbCommand command, TEST test)
        {
		            AddInsertParameters(command, test);
        }
				
		private string GettSelectSQL()
        {
            var sql = string.Format(@" SELECT  {0} 
                                          FROM 
                                                TEST
                                       ", _columnCollectionSelectString);
            return sql;
        }      

		private string GetPagedQuery( string sqlText, int startIndex , int pageSize)
		{
		  	var endIndex = startIndex + pageSize;
          	startIndex = startIndex + 1;

          	var sql = string.Format(@"SELECT TOP {0} {4} 
						FROM ( 
							SELECT    {4} , ROW_NUMBER() OVER ({1}) AS ROW 
						    FROM  TEST ) AS WithRowNumber
					    WHERE
                            ROW BETWEEN {2} AND {3} {1}", pageSize, sqlText, startIndex, endIndex, _columnCollectionSelectString);

          	return sql;		    
		}
		
        private string InsertSQL()
        {
			var nonIdentColumns= ColumnCollections;
		 	var sql = string.Format(@" INSERT INTO
                            TEST (
                              {0}
                            )
                            VALUES
                            (
                                {1}
                            ) ;
                                       ", nonIdentColumns.JoinFormat(",","{0}"), nonIdentColumns.JoinFormat(",", ":{0}"));
                        return sql;
        }

        private string UpdatetSQL()
        {
		 	var sql = string.Format(@" UPDATE
                                 TEST
                            SET
                               {1}
                            WHERE 
                                  {0}
                                       ", WhereClause, ColumnCollections.Except(KeyColumns).ToArray().JoinFormat(" , ", "{0} = :{0}"));
            return sql;
        }

        private string DeletetSQL()
        { 
		 	var sql = string.Format(@" DELETE  FROM
                              TEST
                            WHERE 
                                {0}           
                                       ", WhereClause);
            return sql;
        }
  
	}
	}

